package com.sg.common.util;


import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.wicket.okrframework.common.exception.BizException;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ：CHS
 * @version 1.0
 * @since 2022/3/16 23:16
 */
public class NbDbWoker {

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=a6f2905f5022ce53a7e80bdf48
    public void updateSortNumCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            //修改排序字段
            String sortNum = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                } else if (field.getName().equals("number")) {
                    sortNum = String.valueOf(field.get(param));
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set order_number = ?,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ?";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            ps.setString(1, sortNum);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(2, time);
            ps.setString(3, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(4, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=b8684d80531503cc310589db3e
    public void releaseSingleDataCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set subject_life_cycle = 'CURRENTLY_VALID',release_induction_id = ?,release_time = ?,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ? and subject_life_cycle = 'EDITING'";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            ps.setString(1, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(2, time);
            ps.setString(3, time);
            ps.setString(4, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(5, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=dd0379264cb4a3d23679e6f5d0
    public void archiveBatchSingleDataCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set archivng_type = 'TOBE_ARCHIVING',is_update='TRUE',update_status ='ARCHIVE',archive_induction_id = ?,archive_time = ?,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ? and subject_life_cycle = 'CURRENTLY_VALID'";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            ps.setString(1, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(2, time);
            ps.setString(3, time);
            ps.setString(4, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(5, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void archiveSingleDataCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set is_archive = 'TRUE' ,subject_life_cycle = 'ARCHIVING',archive_induction_id = ?,archive_time = ?,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ? and subject_life_cycle = 'CURRENTLY_VALID'";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            ps.setString(1, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(2, time);
            ps.setString(3, time);
            ps.setString(4, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(5, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void deleteSingleDataCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "delete from " + tableName + " where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ? and subject_life_cycle = 'EDITING'";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            ps.setString(1, priKeyValue);
            ps.execute();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=cac431c190890d4366258c7689
    public void undoArchiveBatchSingleDataCom(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set archivng_type = 'NOT_ARCHIVE',is_update='FALSE',update_status ='UNCHANGED',archive_induction_id = null,archive_time = null,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ? and subject_life_cycle = 'CURRENTLY_VALID'";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(1, time);
            ps.setString(2, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(3, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=d56138760fa540a0b02124ab4f
    public void reviseNotBatchContent(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            //扩展数据---暂时只有找个模式有，其他几个都没有，可以不获取
            List<Object> changeColumnList = null;
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getType().equals(List.class)) {
                    changeColumnList = (List<Object>) field.get(param);
                }
            }
            //拼接sql
            for (Object one : changeColumnList) {
                String upKey = "";
                String upValue = "";

                Class listClasszz = one.getClass();
                Field[] listDeclaredFields = listClasszz.getDeclaredFields();
                for (Field field : listDeclaredFields) {
                    field.setAccessible(true);
                    if (field.getName().equals("tableTypeCode")) {
                        tableName = (String) field.get(param);
                    } else if (field.getName().equals("entityName")) {
                        priKeyFieldName = CommonFunctionHelper.humpToUnderline((String) field.get(param));
                    } else if (field.getName().equals("entityId")) {
                        priKeyValue = (String) field.get(param);
                    }
                    if (field.getName().equals("objectPropertyCode")) {
                        upKey = CommonFunctionHelper.humpToUnderline((String) field.get(one));
                    } else if (field.getName().equals("metaDataColumnContent")) {
                        upValue = (String) field.get(one);
                    }
                }
                if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                    tableName = CommonFunctionHelper.tableInfo.get(tableName);
                }
                String sqlStr = "update " + tableName + " set " + upKey + "= ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ?";
                PreparedStatement ps = conn.prepareStatement(sqlStr);
                ps.setString(1, upValue);
                ps.setString(2, priKeyValue);
                ps.executeUpdate();
                ps.close();
            }

        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    //https://devops.aliyun.com/projex/project/deb7df25fdaa9b97662375a714/task#viewIdentifier=ab07d07c9654b95a147691f2&openWorkitemIdentifier=20aa096c85ff6509492695ead6
    public void undoReviseBatchSingleData(Connection conn, Object param) {
        try {
            //表名称
            String tableName = "";
            //主键字段名称
            String priKeyFieldName = "";
            //主键字段值
            String priKeyValue = "";
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                if (field.getName().equals("objectCode")) {
                    tableName = (String) field.get(param);
                } else if (field.getName().equals("propertyCode")) {
                    priKeyFieldName = (String) field.get(param);
                } else if (field.getName().equals("uniqueId")) {
                    priKeyValue = (String) field.get(param);
                }
            }
            if (CommonFunctionHelper.tableInfo != null && CommonFunctionHelper.tableInfo.containsKey(tableName)) {
                tableName = CommonFunctionHelper.tableInfo.get(tableName);
            }
            String sqlStr = "update " + tableName + " set archivng_type = 'NOT_ARCHIVE',is_update='FALSE',update_status ='UNCHANGED',archive_induction_id = null,archive_time = null,operate_time = ?,operation_induction_id = ? where " + CommonFunctionHelper.humpToUnderline(priKeyFieldName) + " = ?";
            PreparedStatement ps = conn.prepareStatement(sqlStr);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = simpleDateFormat.format(CommonFunctionHelper.getCurrentTime());
            ps.setString(1, time);
            ps.setString(2, CommonFunctionHelper.getBaseInfoFromLocal().getSpecInductionRecordId());
            ps.setString(3, priKeyValue);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new BizException("-1", e.getMessage(), false);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void automicAdd(Connection conn, Object param, String extInfo) {
        try {
            Class classzz = param.getClass();
            TableName ano = (TableName) classzz.getAnnotation(TableName.class);
            if (ano != null) {
                Map<String, String> priKeyInfo = findPriKeyInfo(param);
                if (priKeyInfo == null || priKeyInfo.isEmpty()) {
                    return;
                }
                PreparedStatement ps = conn.prepareStatement("update ? set ? = ? + 1 where ? = ?");
                ps.setString(1, ano.value());
                ps.setString(2, extInfo);
                ps.setString(3, extInfo);
                ps.setString(4, (String) priKeyInfo.keySet().toArray()[0]);
                ps.setString(5, (String) priKeyInfo.keySet().toArray()[1]);
                ps.executeUpdate();
                ps.close();
            }
        } catch (Exception e) {

        }

    }

    public void automicsub(Connection conn, Object param, String extInfo) {
        try {
            Class classzz = param.getClass();
            TableName ano = (TableName) classzz.getAnnotation(TableName.class);
            if (ano != null) {
                Map<String, String> priKeyInfo = findPriKeyInfo(param);
                if (priKeyInfo == null || priKeyInfo.isEmpty()) {
                    return;
                }
                PreparedStatement ps = conn.prepareStatement("update ? set ? = ? - 1 where ? = ?");
                ps.setString(1, ano.value());
                ps.setString(2, extInfo);
                ps.setString(3, extInfo);
                ps.setString(4, (String) priKeyInfo.keySet().toArray()[0]);
                ps.setString(5, (String) priKeyInfo.keySet().toArray()[1]);
                ps.executeUpdate();
                ps.close();
            }
        } catch (Exception e) {

        }

    }

    private Map<String, String> findPriKeyInfo(Object param) {
        Map<String, String> result = new HashMap<>();
        try {
            Class classzz = param.getClass();
            Field[] declaredFields = classzz.getDeclaredFields();
            for (Field field : declaredFields) {
                field.setAccessible(true);
                TableId tableId = field.getAnnotation(TableId.class);
                if (tableId != null) {
                    result.put(tableId.value(), (String) field.get(param));
                }
            }
        } catch (Exception e) {

        }
        return result;
    }
}
